package com.yincheng.oms.bussiness.util.excel;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFPictureData;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.javatuples.Pair;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

public class ExcelReadUtils {
	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel

	/************************************* 文件上传 ****************************/
	/**
	 * 只读取第一个sheet页内容,包括图片数据
	 * @param in
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static Pair<List<Map<String, XSSFPictureData>>,List<List<Object>>> getBankListByExcel(InputStream in, String fileName) throws Exception {
		List<List<Object>> list = null;

		// 创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		list = new ArrayList<List<Object>>();
		// 读取Excel中第一个sheet页内容
		sheet = work.getSheetAt(0);
		if (sheet == null) {
			return null;
		}
		List<Map<String, XSSFPictureData>> pictureMaps = getPictureMaps(sheet);
		// 遍历当前sheet中的所有行
		for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++) {
			row = sheet.getRow(j);
			if (row == null || row.getFirstCellNum() == j) {
				continue;
			}

			// 遍历所有的列
			List<Object> li = new ArrayList<Object>();
			for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
				cell = row.getCell(y);
				li.add(getCellValue(cell));
			}
			list.add(li);
		}
		return Pair.with(pictureMaps, list);
	}
	
	/**
	 * 获取excel文件中的数据集合,只去第一个sheet页
	 * @param in
	 * @param fileName
	 * @return
	 * @throws Exception
	 */
	public static List<List<Object>> getListByExcel(InputStream in, String fileName) throws Exception {
		List<List<Object>> list = null;

		// 创建Excel工作薄
		Workbook work = getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet = null;
		Row row = null;
		Cell cell = null;

		list = new ArrayList<List<Object>>();
		// 读取Excel中第一个sheet页内容
		sheet = work.getSheetAt(0);
		if (sheet == null) {
			return null;
		}
		// 遍历当前sheet中的所有行
		int cellNum = sheet.getRow(0).getLastCellNum();//设定每行数据个数
		for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum()+1; j++) {
			row = sheet.getRow(j);
			if (row == null || row.getFirstCellNum() == j || row.getCell(0) == null) {
				continue;
			}
			// 遍历所有的列
			List<Object> li = new ArrayList<Object>();
			for (int y = 0; y < cellNum; y++) {
				cell = row.getCell(y);
				if(cell == null)
					li.add(null);
				else
					li.add(getCellValue(cell));
			}
			list.add(li);
		}
		return list;
	} 

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	public static Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd HH:mm:SS"); // 日期格式化
//		SimpleDateFormat sdf2 = new SimpleDateFormat("yyy-MM-dd HH:mm");
//		SimpleDateFormat sdf3 = new SimpleDateFormat("yyy-MM-dd HH:mm:SS");
//		DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字

		switch (cell.getCellTypeEnum()) {
		case STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case NUMERIC:
			String dataFormatString = cell.getCellStyle().getDataFormatString();
			if ("General".equals(dataFormatString)) {
				value = df.format(cell.getNumericCellValue());
			} else if ("m/d/yy".equals(dataFormatString)) {
				value = sdf.format(cell.getDateCellValue());
			} else if ("m/d/yy h:mm".equals(dataFormatString)) {
				value = sdf.format(cell.getDateCellValue());
			} else if ("m/d/yy h:mm:ss".equals(dataFormatString)) {
				value = sdf.format(cell.getDateCellValue());
			} else {
				value = df.format(cell.getNumericCellValue());
			}
			break;
		case BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case BLANK:
			value = null;
			break;
		default:
			break;
		}
		return value;
	}

	public static List<Map<String, XSSFPictureData>> getPictureMaps(Sheet sheet) throws IOException {
		List<Map<String, XSSFPictureData>> pics = new ArrayList<>();
		List<POIXMLDocumentPart> list = ((POIXMLDocumentPart) sheet).getRelations();
		for (POIXMLDocumentPart part : list) {
			if (part instanceof XSSFDrawing) {
				XSSFDrawing drawing = (XSSFDrawing) part;
				List<XSSFShape> shapes = drawing.getShapes();
				for (XSSFShape shape : shapes) {
					Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
					XSSFPicture picture = (XSSFPicture) shape;
					XSSFClientAnchor anchor = picture.getPreferredSize();
					CTMarker marker = anchor.getFrom();
					String key = marker.getRow() + "-" + marker.getCol();
					map.put(key, picture.getPictureData());
					pics.add(map);
				}
			}
		}
		return pics;
	}
	
}
